package com.ruoyi.system.service.impl;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import org.apache.commons.io.IOUtils;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import com.ruoyi.system.mapper.SyncDatabaseMapper;
import com.ruoyi.system.domain.datasync.SyncDatabase;
import com.ruoyi.system.domain.datasync.SyncServer;
import com.ruoyi.system.mapper.SyncServerMapper;
import com.ruoyi.system.service.ISyncDatabaseService;
import com.ruoyi.system.utils.SqlBackupGenerator;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.file.FileUtils;
import com.jcraft.jsch.ChannelExec;
import com.jcraft.jsch.ChannelSftp;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import java.io.OutputStream;
import java.lang.ProcessBuilder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;
import java.util.Calendar;
import com.ruoyi.system.domain.datasync.SyncDbSync;
import com.ruoyi.system.mapper.SyncDbSyncMapper;

/**
 * 数据库Service业务层处理
 * 
 * @author ruoyi
 */
@Service
public class SyncDatabaseServiceImpl implements ISyncDatabaseService 
{
    private static final Logger log = LoggerFactory.getLogger(SyncDatabaseServiceImpl.class);
    
    @Value("${ruoyi.profile}")
    private String profilePath;
    
    @Autowired
    private SyncDatabaseMapper syncDatabaseMapper;

    @Autowired
    private SyncServerMapper syncServerMapper;

    @Autowired
    private SyncDbSyncMapper syncDbSyncMapper;

    /**
     * 查询数据库
     * 
     * @param dbId 数据库主键
     * @return 数据库
     */
    @Override
    public SyncDatabase selectSyncDatabaseByDbId(Long dbId)
    {
        return syncDatabaseMapper.selectSyncDatabaseByDbId(dbId);
    }

    /**
     * 查询数据库列表
     * 
     * @param syncDatabase 数据库
     * @return 数据库
     */
    @Override
    public List<SyncDatabase> selectSyncDatabaseList(SyncDatabase syncDatabase)
    {
        return syncDatabaseMapper.selectSyncDatabaseList(syncDatabase);
    }

    /**
     * 新增数据库
     * 
     * @param syncDatabase 数据库
     * @return 结果
     */
    @Override
    public int insertSyncDatabase(SyncDatabase syncDatabase)
    {
        syncDatabase.setCreateTime(DateUtils.getNowDate());
        return syncDatabaseMapper.insertSyncDatabase(syncDatabase);
    }

    /**
     * 修改数据库
     * 
     * @param syncDatabase 数据库
     * @return 结果
     */
    @Override
    public int updateSyncDatabase(SyncDatabase syncDatabase)
    {
        syncDatabase.setUpdateTime(DateUtils.getNowDate());
        return syncDatabaseMapper.updateSyncDatabase(syncDatabase);
    }

    /**
     * 批量删除数据库
     * 
     * @param dbIds 需要删除的数据库主键
     * @return 结果
     */
    @Override
    public int deleteSyncDatabaseByDbIds(Long[] dbIds)
    {
        return syncDatabaseMapper.deleteSyncDatabaseByDbIds(dbIds);
    }

    /**
     * 删除数据库信息
     * 
     * @param dbId 数据库主键
     * @return 结果
     */
    @Override
    public int deleteSyncDatabaseByDbId(Long dbId)
    {
        return syncDatabaseMapper.deleteSyncDatabaseByDbId(dbId);
    }

    /**
     * 测试数据库连接
     * 
     * @param dbId 数据库主键
     * @return 结果
     */
    @Override
    public boolean testDatabaseConnection(Long dbId)
    {
        SyncDatabase database = syncDatabaseMapper.selectSyncDatabaseByDbId(dbId);
        if (database == null) {
            return false;
        }
        
        // 获取服务器信息，以获取IP地址
        SyncServer server = syncServerMapper.selectSyncServerByServerId(database.getServerId());
        if (server == null) {
            return false;
        }
        
        Connection conn = null;
        try {
            String dbType = database.getDbType();
            String url = "";
            String driverClassName = "";
            
            // 根据数据库类型构建连接URL和驱动类名
            switch (dbType.toLowerCase()) {
                case "mysql": // MySQL
                    driverClassName = "com.mysql.cj.jdbc.Driver";
                    url = "jdbc:mysql://" + server.getIpAddress() + ":" + database.getDbPort() + "/" + database.getDbName() + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true";
                    break;
                case "oracle": // Oracle
                    driverClassName = "oracle.jdbc.driver.OracleDriver";
                    url = "jdbc:oracle:thin:@" + server.getIpAddress() + ":" + database.getDbPort() + ":" + database.getDbName();
                    break;
                case "sqlserver": // SQLServer
                    driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
                    url = "jdbc:sqlserver://" + server.getIpAddress() + ":" + database.getDbPort() + ";databaseName=" + database.getDbName();
                    break;
                case "postgresql": // PostgreSQL
                    driverClassName = "org.postgresql.Driver";
                    url = "jdbc:postgresql://" + server.getIpAddress() + ":" + database.getDbPort() + "/" + database.getDbName();
                    break;
                case "dameng": // 达梦数据库
                    driverClassName = "dm.jdbc.driver.DmDriver";
                    url = "jdbc:dm://" + server.getIpAddress() + ":" + database.getDbPort() + "/" + database.getDbName();
                    break;
                default:
                    return false;
            }
            
            Class.forName(driverClassName);
            conn = DriverManager.getConnection(url, database.getDbUsername(), database.getDbPassword());
            return true;
        } catch (Exception e) {
            log.error("数据库连接失败", e);
            return false;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    // 忽略关闭异常
                }
            }
        }
    }

    /**
     * 获取所有可用数据库列表（下拉选项用）
     * 
     * @return 数据库选项列表
     */
    @Override
    public List<SyncDatabase> selectDatabaseOptions()
    {
        SyncDatabase database = new SyncDatabase();
        database.setStatus("0"); // 状态正常的数据库
        return syncDatabaseMapper.selectSyncDatabaseList(database);
    }

    /**
     * 本地备份数据库
     * 
     * @param dbId 数据库主键
     * @return 备份文件路径
     */
    @Override
    public String backupDatabase(Long dbId) {
        // 获取数据库信息
        SyncDatabase database = selectSyncDatabaseByDbId(dbId);
        if (database == null) {
            log.error("备份数据库失败：数据库[{}]不存在", dbId);
            return null;
        }

        // 获取服务器信息
        SyncServer server = syncServerMapper.selectSyncServerByServerId(database.getServerId());
        if (server == null) {
            log.error("备份数据库失败：服务器[{}]不存在", database.getServerId());
            return null;
        }

        String backupPath = null;
        Connection connection = null;
        try {
            // 根据数据库类型选择不同的备份方式
            String dbType = database.getDbType();
            if ("mysql".equalsIgnoreCase(dbType)) {
                backupPath = backupMySql(database, server);
            } else if ("oracle".equalsIgnoreCase(dbType)) {
                backupPath = backupOracle(database, server);
            } else if ("sqlserver".equalsIgnoreCase(dbType)) {
                backupPath = backupSqlServer(database, server);
            } else if ("postgresql".equalsIgnoreCase(dbType)) {
                backupPath = backupPostgreSql(database, server);
            } else if ("dameng".equalsIgnoreCase(dbType)) {
                backupPath = backupDameng(database, server);
            } else {
                log.error("备份数据库失败：不支持的数据库类型[{}]", dbType);
                return null;
            }
            
            log.info("备份数据库[{}]成功，备份文件路径: {}", database.getDbName(), backupPath);
            return backupPath;
        } catch (Exception e) {
            log.error("备份数据库[{}]失败", database.getDbName(), e);
            return null;
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.error("关闭数据库连接失败", e);
                }
            }
        }
    }

    /**
     * 远程备份数据库
     * 
     * @param dbId 数据库主键
     * @param targetServerId 目标服务器ID
     * @param backupPath 备份路径，为空则使用默认路径
     * @return 备份文件路径
     */
    @Override
    public String remoteBackupDatabase(Long dbId, Long targetServerId, String backupPath) {
        // 获取数据库信息
        SyncDatabase database = selectSyncDatabaseByDbId(dbId);
        if (database == null) {
            log.error("远程备份数据库失败：数据库[{}]不存在", dbId);
            return null;
        }

        // 获取源服务器信息
        SyncServer sourceServer = syncServerMapper.selectSyncServerByServerId(database.getServerId());
        if (sourceServer == null) {
            log.error("远程备份数据库失败：源服务器[{}]不存在", database.getServerId());
            return null;
        }

        // 获取目标服务器信息
        SyncServer targetServer = syncServerMapper.selectSyncServerByServerId(targetServerId);
        if (targetServer == null) {
            log.error("远程备份数据库失败：目标服务器[{}]不存在", targetServerId);
            return null;
        }

        try {
            // 1. 先在本地备份数据库
            String localBackupPath = backupDatabase(dbId);
            if (localBackupPath == null) {
                log.error("远程备份数据库失败：本地备份失败");
                return null;
            }

            // 2. 创建目标服务器的备份目录
            String targetBackupDir;
            if (backupPath != null && !backupPath.isEmpty()) {
                targetBackupDir = backupPath;
            } else if (targetServer.getBackupPath() != null && !targetServer.getBackupPath().isEmpty()) {
                targetBackupDir = targetServer.getBackupPath();
            } else {
                targetBackupDir = "/backup/database";
            }
            
            // 3. 确保目标目录存在
            boolean dirExists = ensureRemoteDirectoryExists(targetServer, targetBackupDir);
            if (!dirExists) {
                log.error("远程备份数据库失败：无法创建目标服务器上的备份目录");
                return null;
            }

            // 4. 生成唯一的目标文件名
            String fileName = new File(localBackupPath).getName();
            String targetFilePath = targetBackupDir;
            if (!targetBackupDir.endsWith("/")) {
                targetFilePath += "/";
            }
            targetFilePath += fileName;

            // 5. 使用SCP或SFTP将本地备份文件传输到目标服务器
            boolean transferSuccess = transferFileToRemoteServer(
                localBackupPath, targetServer, targetFilePath);

            if (transferSuccess) {
                log.info("远程备份数据库[{}]成功，备份文件路径: {}", database.getDbName(), targetFilePath);
                return targetFilePath;
            } else {
                log.error("远程备份数据库[{}]失败：文件传输失败", database.getDbName());
                return null;
            }
        } catch (Exception e) {
            log.error("远程备份数据库[{}]失败", database.getDbName(), e);
            return null;
        }
    }

    /**
     * 确保远程目录存在
     * 
     * @param server 服务器信息
     * @param dirPath 目录路径
     * @return 目录是否存在或创建成功
     */
    private boolean ensureRemoteDirectoryExists(SyncServer server, String dirPath) {
        Session session = null;
        ChannelExec channel = null;
        
        try {
            JSch jsch = new JSch();
            session = jsch.getSession(server.getUserName(), server.getIpAddress(), Integer.parseInt(server.getPort()));
            session.setPassword(server.getPassword());
            session.setConfig("StrictHostKeyChecking", "no");
            session.connect(30000);
            
            // 先检查目录是否存在
            channel = (ChannelExec) session.openChannel("exec");
            channel.setCommand("[ -d \"" + dirPath + "\" ] && echo \"exists\" || echo \"not exists\"");
            
            InputStream in = channel.getInputStream();
            channel.connect();
            
            byte[] tmp = new byte[1024];
            StringBuilder result = new StringBuilder();
            while (true) {
                while (in.available() > 0) {
                    int i = in.read(tmp, 0, 1024);
                    if (i < 0) break;
                    result.append(new String(tmp, 0, i));
                }
                if (channel.isClosed()) {
                    break;
                }
                try {
                    Thread.sleep(100);
                } catch (Exception ee) {
                    // 忽略异常
                }
            }
            
            boolean exists = result.toString().trim().equals("exists");
            if (exists) {
                return true;
            }
            
            // 如果目录不存在，则创建目录
            channel.disconnect();
            channel = (ChannelExec) session.openChannel("exec");
            channel.setCommand("mkdir -p \"" + dirPath + "\" && echo \"success\" || echo \"failed\"");
            
            in = channel.getInputStream();
            channel.connect();
            
            result = new StringBuilder();
            while (true) {
                while (in.available() > 0) {
                    int i = in.read(tmp, 0, 1024);
                    if (i < 0) break;
                    result.append(new String(tmp, 0, i));
                }
                if (channel.isClosed()) {
                    break;
                }
                try {
                    Thread.sleep(100);
                } catch (Exception ee) {
                    // 忽略异常
                }
            }
            
            return result.toString().trim().equals("success");
        } catch (Exception e) {
            log.error("确保远程目录存在失败", e);
            return false;
        } finally {
            if (channel != null) {
                channel.disconnect();
            }
            if (session != null) {
                session.disconnect();
            }
        }
    }

    /**
     * 将文件传输到远程服务器
     * 
     * @param localFilePath 本地文件路径
     * @param server 目标服务器信息
     * @param remoteFilePath 远程文件路径
     * @return 传输是否成功
     */
    private boolean transferFileToRemoteServer(String localFilePath, SyncServer server, String remoteFilePath) {
        Session session = null;
        ChannelSftp channelSftp = null;
        
        try {
            JSch jsch = new JSch();
            session = jsch.getSession(server.getUserName(), server.getIpAddress(), Integer.parseInt(server.getPort()));
            session.setPassword(server.getPassword());
            session.setConfig("StrictHostKeyChecking", "no");
            session.connect(30000);
            
            // 使用SFTP通道传输文件
            channelSftp = (ChannelSftp) session.openChannel("sftp");
            channelSftp.connect(30000);
            
            // 上传文件
            channelSftp.put(localFilePath, remoteFilePath);
            
            return true;
        } catch (Exception e) {
            log.error("将文件传输到远程服务器失败", e);
            return false;
        } finally {
            if (channelSftp != null) {
                channelSftp.disconnect();
            }
            if (session != null) {
                session.disconnect();
            }
        }
    }

    /**
     * 获取数据库表结构
     * 
     * @param dbId 数据库主键
     * @return 表结构列表
     */
    @Override
    public List<Map<String, Object>> getDatabaseTables(Long dbId)
    {
        SyncDatabase database = syncDatabaseMapper.selectSyncDatabaseByDbId(dbId);
        if (database == null) {
            log.error("数据库不存在，dbId={}", dbId);
            return new ArrayList<>();
        }
        
        SyncServer server = syncServerMapper.selectSyncServerByServerId(database.getServerId());
        if (server == null) {
            log.error("服务器不存在，serverId={}", database.getServerId());
            return new ArrayList<>();
        }
        
        List<Map<String, Object>> tables = new ArrayList<>();
        Connection conn = null;
        ResultSet rs = null;
        
        try {
            conn = getConnection(database, server);
            DatabaseMetaData metaData = conn.getMetaData();
            
            // 获取所有表
            rs = metaData.getTables(database.getDbName(), null, "%", new String[]{"TABLE"});
            
            while (rs.next()) {
                Map<String, Object> table = new HashMap<>();
                table.put("tableName", rs.getString("TABLE_NAME"));
                table.put("tableType", rs.getString("TABLE_TYPE"));
                table.put("remarks", rs.getString("REMARKS"));
                tables.add(table);
            }
            
            return tables;
        } catch (Exception e) {
            log.error("获取数据库表结构失败", e);
            return new ArrayList<>();
        } finally {
            closeConnection(conn, null, rs);
        }
    }
    
    /**
     * 获取表字段结构
     * 
     * @param dbId 数据库主键
     * @param tableName 表名
     * @return 字段结构列表
     */
    @Override
    public List<Map<String, Object>> getTableColumns(Long dbId, String tableName)
    {
        SyncDatabase database = syncDatabaseMapper.selectSyncDatabaseByDbId(dbId);
        if (database == null) {
            log.error("数据库不存在，dbId={}", dbId);
            return new ArrayList<>();
        }
        
        SyncServer server = syncServerMapper.selectSyncServerByServerId(database.getServerId());
        if (server == null) {
            log.error("服务器不存在，serverId={}", database.getServerId());
            return new ArrayList<>();
        }
        
        List<Map<String, Object>> columns = new ArrayList<>();
        Connection conn = null;
        ResultSet rs = null;
        
        try {
            conn = getConnection(database, server);
            DatabaseMetaData metaData = conn.getMetaData();
            
            // 获取主键信息
            List<String> primaryKeys = new ArrayList<>();
            try (ResultSet pkRs = metaData.getPrimaryKeys(database.getDbName(), null, tableName)) {
                while (pkRs.next()) {
                    primaryKeys.add(pkRs.getString("COLUMN_NAME"));
                }
            }
            
            // 获取表的所有列
            rs = metaData.getColumns(database.getDbName(), null, tableName, "%");
            
            while (rs.next()) {
                Map<String, Object> column = new HashMap<>();
                String columnName = rs.getString("COLUMN_NAME");
                column.put("columnName", columnName);
                column.put("dataType", rs.getString("TYPE_NAME"));
                column.put("columnSize", rs.getInt("COLUMN_SIZE"));
                column.put("nullable", rs.getBoolean("NULLABLE"));
                column.put("remarks", rs.getString("REMARKS"));
                column.put("isPrimaryKey", primaryKeys.contains(columnName));
                columns.add(column);
            }
            
            return columns;
        } catch (Exception e) {
            log.error("获取表字段结构失败", e);
            return new ArrayList<>();
        } finally {
            closeConnection(conn, null, rs);
        }
    }
    
    /**
     * 执行SQL查询
     * 
     * @param dbId 数据库主键
     * @param sql SQL语句
     * @return 查询结果
     */
    @Override
    public Map<String, Object> executeQuery(Long dbId, String sql)
    {
        SyncDatabase database = syncDatabaseMapper.selectSyncDatabaseByDbId(dbId);
        if (database == null) {
            log.error("数据库不存在，dbId={}", dbId);
            return createErrorResult("数据库不存在");
        }
        
        SyncServer server = syncServerMapper.selectSyncServerByServerId(database.getServerId());
        if (server == null) {
            log.error("服务器不存在，serverId={}", database.getServerId());
            return createErrorResult("服务器不存在");
        }
        
        // 检查SQL语句是否为查询语句
        sql = sql.trim();
        if (!sql.toLowerCase().startsWith("select") && !sql.toLowerCase().startsWith("show")) {
            return createErrorResult("只允许执行SELECT或SHOW查询语句");
        }
        
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = getConnection(database, server);
            stmt = conn.createStatement();
            
            // 设置查询超时和最大行数
            stmt.setQueryTimeout(30);
            stmt.setMaxRows(1000);
            
            rs = stmt.executeQuery(sql);
            
            // 获取结果集元数据
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            
            // 获取列名
            List<String> columnNames = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                columnNames.add(metaData.getColumnName(i));
            }
            
            // 获取数据行
            List<Map<String, Object>> rows = new ArrayList<>();
            while (rs.next()) {
                Map<String, Object> row = new LinkedHashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }
                rows.add(row);
            }
            
            Map<String, Object> result = new HashMap<>();
            result.put("success", true);
            result.put("message", "查询成功");
            result.put("columnNames", columnNames);
            result.put("rows", rows);
            result.put("total", rows.size());
            
            return result;
        } catch (Exception e) {
            log.error("执行SQL查询失败", e);
            return createErrorResult("执行SQL查询失败: " + e.getMessage());
        } finally {
            closeConnection(conn, stmt, rs);
        }
    }
    
    /**
     * 创建错误结果
     */
    private Map<String, Object> createErrorResult(String message) {
        Map<String, Object> result = new HashMap<>();
        result.put("success", false);
        result.put("message", message);
        return result;
    }
    
    /**
     * 获取数据库连接
     */
    private Connection getConnection(SyncDatabase database, SyncServer server) throws Exception {
        String dbType = database.getDbType();
        String url = "";
        String driverClassName = "";
        
        // 根据数据库类型构建连接URL和驱动类名
        switch (dbType.toLowerCase()) {
            case "mysql": // MySQL
                driverClassName = "com.mysql.cj.jdbc.Driver";
                url = "jdbc:mysql://" + server.getIpAddress() + ":" + database.getDbPort() + "/" + database.getDbName() + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true";
                break;
            case "oracle": // Oracle
                driverClassName = "oracle.jdbc.driver.OracleDriver";
                url = "jdbc:oracle:thin:@" + server.getIpAddress() + ":" + database.getDbPort() + ":" + database.getDbName();
                break;
            case "sqlserver": // SQLServer
                driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
                url = "jdbc:sqlserver://" + server.getIpAddress() + ":" + database.getDbPort() + ";databaseName=" + database.getDbName();
                break;
            case "postgresql": // PostgreSQL
                driverClassName = "org.postgresql.Driver";
                url = "jdbc:postgresql://" + server.getIpAddress() + ":" + database.getDbPort() + "/" + database.getDbName();
                break;
            case "dameng": // 达梦数据库
                driverClassName = "dm.jdbc.driver.DmDriver";
                url = "jdbc:dm://" + server.getIpAddress() + ":" + database.getDbPort() + "/" + database.getDbName();
                break;
            default:
                throw new SQLException("不支持的数据库类型：" + dbType);
        }
        
        Class.forName(driverClassName);
        return DriverManager.getConnection(url, database.getDbUsername(), database.getDbPassword());
    }
    
    /**
     * 关闭数据库连接资源
     */
    private void closeConnection(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                // 忽略关闭异常
            }
        }
        
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                // 忽略关闭异常
            }
        }
        
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // 忽略关闭异常
            }
        }
    }

    /**
     * 备份MySQL数据库 - 使用SQL脚本生成器
     *
     * @param database 数据库信息
     * @param server 服务器信息
     * @return 备份文件路径
     */
    private String backupMySql(SyncDatabase database, SyncServer server) throws Exception {
        String backupFolder = profilePath + "/backup/database/" + DateUtils.datePath() + "/";
        String backupFileName = database.getDbName() + "_" + DateUtils.dateTimeNow() + ".sql";
        String backupFilePath = backupFolder + backupFileName;

        // 确保备份目录存在
        File backupDir = new File(backupFolder);
        if (!backupDir.exists()) {
            backupDir.mkdirs();
        }

        log.info("开始使用SQL脚本生成器备份MySQL数据库 [{}]", database.getDbName());

        Connection connection = null;
        try {
            // 建立数据库连接
            String url = "jdbc:mysql://" + server.getIpAddress() + ":" + database.getDbPort() + "/" + database.getDbName() +
                    "?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8";

            connection = DriverManager.getConnection(url, database.getDbUsername(), database.getDbPassword());

            // 配置备份选项
            SqlBackupGenerator.BackupConfig config = new SqlBackupGenerator.BackupConfig();
            config.setIncludeStructure(true);
            config.setIncludeData(true);
            config.setDropTables(true);
            config.setAddComments(true);
            config.setCharset("utf8mb4");
            config.setDatabaseType(SqlBackupGenerator.DatabaseType.MYSQL);

            // 创建进度回调
            SqlBackupGenerator.ProgressCallback callback = createProgressCallback();

            // 执行备份
            boolean success = SqlBackupGenerator.generateBackup(connection, database.getDbName(),
                    backupFilePath, config, callback);

            if (success) {
                log.info("MySQL数据库备份成功，文件路径：{}", backupFilePath);
                return backupFilePath;
            } else {
                log.error("MySQL数据库备份失败");
                return null;
            }
        } catch (Exception e) {
            log.error("MySQL数据库备份失败", e);
            return null;
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.error("关闭数据库连接失败", e);
                }
            }
        }


    }

    /**
     * 备份Oracle数据库
     * 
     * @param database 数据库信息
     * @param server 服务器信息
     * @return 备份文件路径
     */
    private String backupOracle(SyncDatabase database, SyncServer server) throws Exception {
        String backupFolder = profilePath + "/backup/database/" + DateUtils.datePath() + "/";
        String backupFileName = database.getDbName() + "_" + DateUtils.dateTimeNow() + ".dmp";
        String backupFilePath = backupFolder + backupFileName;
        
        // 确保备份目录存在
        File backupDir = new File(backupFolder);
        if (!backupDir.exists()) {
            backupDir.mkdirs();
        }
        
        // 使用exp命令备份Oracle
        String command = String.format(
            "exp %s/%s@%s:%s/%s file=%s",
            database.getDbUsername(),
            database.getDbPassword(),
            server.getIpAddress(),
            database.getDbPort(),
            database.getDbName(),
            backupFilePath
        );
        
        Process process = Runtime.getRuntime().exec(new String[]{"cmd.exe", "/c", command});
        int exitCode = process.waitFor();
        
        if (exitCode != 0) {
            log.error("Oracle数据库备份失败，退出码：{}", exitCode);
            return null;
        }
        
        return backupFilePath;
    }

    /**
     * 备份SQL Server数据库
     * 
     * @param database 数据库信息
     * @param server 服务器信息
     * @return 备份文件路径
     */
    private String backupSqlServer(SyncDatabase database, SyncServer server) throws Exception {
        String backupFolder = profilePath + "/backup/database/" + DateUtils.datePath() + "/";
        String backupFileName = database.getDbName() + "_" + DateUtils.dateTimeNow() + ".bak";
        String backupFilePath = backupFolder + backupFileName;
        
        // 确保备份目录存在
        File backupDir = new File(backupFolder);
        if (!backupDir.exists()) {
            backupDir.mkdirs();
        }
        
        // SQL Server使用SQL语句备份
        Connection conn = null;
        Statement stmt = null;
        
        try {
            conn = getConnection(database, server);
            stmt = conn.createStatement();
            
            String sql = String.format("BACKUP DATABASE [%s] TO DISK = N'%s'", 
                database.getDbName(), backupFilePath);
            
            stmt.execute(sql);
            return backupFilePath;
        } finally {
            closeConnection(conn, stmt, null);
        }
    }

    /**
     * 备份PostgreSQL数据库 - 使用SQL脚本生成器
     *
     * @param database 数据库信息
     * @param server 服务器信息
     * @return 备份文件路径
     */
    private String backupPostgreSql(SyncDatabase database, SyncServer server) throws Exception {
        String backupFolder = profilePath + "/backup/database/" + DateUtils.datePath() + "/";
        String backupFileName = database.getDbName() + "_" + DateUtils.dateTimeNow() + ".sql";
        String backupFilePath = backupFolder + backupFileName;

        // 确保备份目录存在
        File backupDir = new File(backupFolder);
        if (!backupDir.exists()) {
            backupDir.mkdirs();
        }

        log.info("开始使用SQL脚本生成器备份PostgreSQL数据库 [{}]", database.getDbName());

        Connection connection = null;
        try {
            // 建立数据库连接
            String url = "jdbc:postgresql://" + server.getIpAddress() + ":" + database.getDbPort() + "/" + database.getDbName();

            connection = DriverManager.getConnection(url, database.getDbUsername(), database.getDbPassword());

            // 配置备份选项
            SqlBackupGenerator.BackupConfig config = new SqlBackupGenerator.BackupConfig();
            config.setIncludeStructure(true);
            config.setIncludeData(true);
            config.setDropTables(true);
            config.setAddComments(true);
            config.setCharset("utf8");
            config.setDatabaseType(SqlBackupGenerator.DatabaseType.POSTGRESQL);

            // 创建进度回调
            SqlBackupGenerator.ProgressCallback callback = createProgressCallback();

            // 执行备份
            boolean success = SqlBackupGenerator.generateBackup(connection, database.getDbName(),
                    backupFilePath, config, callback);

            if (success) {
                log.info("PostgreSQL数据库备份成功，文件路径：{}", backupFilePath);
                return backupFilePath;
            } else {
                log.error("PostgreSQL数据库备份失败");
                return null;
            }
        } catch (Exception e) {
            log.error("PostgreSQL数据库备份失败", e);
            return null;
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.error("关闭数据库连接失败", e);
                }
            }
        }
    }

    /**
     * 备份达梦数据库
     *
     * @param database 数据库信息
     * @param server 服务器信息
     * @return 备份文件路径
     */
    private String backupDameng(SyncDatabase database, SyncServer server) throws Exception {
        String backupFolder = profilePath + "/backup/database/" + DateUtils.datePath() + "/";
        String backupFileName = database.getDbName() + "_" + DateUtils.dateTimeNow() + ".sql";
        String backupFilePath = backupFolder + backupFileName;

        // 确保备份目录存在
        File backupDir = new File(backupFolder);
        if (!backupDir.exists()) {
            backupDir.mkdirs();
        }

        log.info("开始使用SQL脚本生成器备份达梦数据库 [{}]", database.getDbName());

        Connection connection = null;
        try {
            // 建立数据库连接
            String url = "jdbc:dm://" + server.getIpAddress() + ":" + database.getDbPort() + "/" + database.getDbName();

            connection = DriverManager.getConnection(url, database.getDbUsername(), database.getDbPassword());

            // 配置备份选项
            SqlBackupGenerator.BackupConfig config = new SqlBackupGenerator.BackupConfig();
            config.setIncludeStructure(true);
            config.setIncludeData(true);
            config.setDropTables(true);
            config.setAddComments(true);
            config.setCharset("utf8");
            config.setDatabaseType(SqlBackupGenerator.DatabaseType.DAMENG);

            // 创建进度回调
            SqlBackupGenerator.ProgressCallback callback = createProgressCallback();

            // 执行备份
            boolean success = SqlBackupGenerator.generateBackup(connection, database.getDbName(),
                    backupFilePath, config, callback);

            if (success) {
                log.info("达梦数据库备份成功，文件路径：{}", backupFilePath);
                return backupFilePath;
            } else {
                log.error("达梦数据库备份失败");
                return null;
            }
        } catch (Exception e) {
            log.error("达梦数据库备份失败", e);
            return null;
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.error("关闭数据库连接失败", e);
                }
            }
        }
    }

    /**
     * 更新数据库定时备份设置
     *
     * @param database 数据库
     * @return 结果
     */
    @Override
    public int updateDatabaseBackupSettings(SyncDatabase database) {
        database.setUpdateTime(DateUtils.getNowDate());
        int result = syncDatabaseMapper.updateSyncDatabase(database);

        // 如果启用了定时备份，自动创建或更新对应的同步任务
        if ("1".equals(database.getScheduleBackup()) && result > 0) {
            createOrUpdateBackupSyncTask(database);
        } else if ("0".equals(database.getScheduleBackup())) {
            // 如果禁用了定时备份，删除对应的同步任务
            deleteBackupSyncTask(database.getDbId());
        }

        return result;
    }

    /**
     * 创建或更新备份同步任务
     *
     * @param database 数据库信息
     */
    private void createOrUpdateBackupSyncTask(SyncDatabase database) {
        try {
            // 查找是否已存在备份同步任务（通过特殊的命名规则识别）
            String backupTaskName = "备份任务-" + database.getDbName();
            SyncDbSync existingTask = findBackupSyncTask(database.getDbId());

            if (existingTask != null) {
                // 更新现有任务
                updateExistingBackupTask(existingTask, database);
                log.info("更新数据库[{}]的备份同步任务", database.getDbName());
            } else {
                // 创建新任务
                createNewBackupTask(database, backupTaskName);
                log.info("为数据库[{}]创建新的备份同步任务", database.getDbName());
            }
        } catch (Exception e) {
            log.error("创建或更新数据库[{}]的备份同步任务失败", database.getDbName(), e);
        }
    }

    /**
     * 删除备份同步任务
     *
     * @param dbId 数据库ID
     */
    private void deleteBackupSyncTask(Long dbId) {
        try {
            SyncDbSync existingTask = findBackupSyncTask(dbId);
            if (existingTask != null) {
                syncDbSyncMapper.deleteSyncDbSyncBySyncId(existingTask.getSyncId());
                log.info("删除数据库ID[{}]的备份同步任务", dbId);
            }
        } catch (Exception e) {
            log.error("删除数据库ID[{}]的备份同步任务失败", dbId, e);
        }
    }

    /**
     * 查找备份同步任务
     *
     * @param dbId 数据库ID
     * @return 备份同步任务
     */
    private SyncDbSync findBackupSyncTask(Long dbId) {
        SyncDbSync query = new SyncDbSync();
        query.setSourceDbId(dbId);
        query.setTargetDbId(dbId); // 备份任务的源和目标是同一个数据库
        List<SyncDbSync> tasks = syncDbSyncMapper.selectSyncDbSyncList(query);

        // 查找备份任务（通过命名规则识别）
        for (SyncDbSync task : tasks) {
            if (task.getSyncName() != null && task.getSyncName().startsWith("备份任务-")) {
                return task;
            }
        }
        return null;
    }

    /**
     * 更新现有备份任务
     *
     * @param existingTask 现有任务
     * @param database 数据库信息
     */
    private void updateExistingBackupTask(SyncDbSync existingTask, SyncDatabase database) {
        // 将备份周期转换为执行周期
        String execCycle = convertBackupCycleToExecCycle(database.getBackupCycle());

        existingTask.setExecCycle(execCycle);
        existingTask.setExecTime(database.getBackupTime());
        existingTask.setStatus("0"); // 启用状态
        existingTask.setUpdateTime(DateUtils.getNowDate());

        syncDbSyncMapper.updateSyncDbSync(existingTask);
    }

    /**
     * 创建新的备份任务
     *
     * @param database 数据库信息
     * @param taskName 任务名称
     */
    private void createNewBackupTask(SyncDatabase database, String taskName) {
        SyncDbSync backupTask = new SyncDbSync();
        backupTask.setSyncName(taskName);
        backupTask.setSourceDbId(database.getDbId());
        backupTask.setSourceDbName(database.getDbName());
        backupTask.setTargetDbId(database.getDbId()); // 备份任务的源和目标是同一个数据库
        backupTask.setTargetDbName(database.getDbName());
        backupTask.setSyncType("1"); // 自动同步
        backupTask.setSyncMode("1"); // 全量同步（备份通常是全量的）

        // 将备份周期转换为执行周期
        String execCycle = convertBackupCycleToExecCycle(database.getBackupCycle());
        backupTask.setExecCycle(execCycle);
        backupTask.setExecTime(database.getBackupTime());

        backupTask.setStatus("0"); // 启用状态
        backupTask.setCreateBy("system");
        backupTask.setCreateTime(DateUtils.getNowDate());
        backupTask.setRemark("系统自动创建的数据库备份任务");

        syncDbSyncMapper.insertSyncDbSync(backupTask);
    }

    /**
     * 将备份周期转换为执行周期
     *
     * @param backupCycle 备份周期（1每天 2每周 3每月）
     * @return 执行周期（0手动 1每天 2每周 3每月）
     */
    private String convertBackupCycleToExecCycle(String backupCycle) {
        if ("1".equals(backupCycle)) {
            return "1"; // 每天
        } else if ("2".equals(backupCycle)) {
            return "2"; // 每周
        } else if ("3".equals(backupCycle)) {
            return "3"; // 每月
        }
        return "0"; // 默认手动
    }

    /**
     * 执行定时备份任务
     * 注意：此方法已废弃，备份任务现在通过 sync_db_sync 表和 SyncDbTask 统一管理
     * 保留此方法仅为兼容性考虑
     */
    @Override
    @Deprecated
    public void executeScheduledBackup() {
        log.warn("executeScheduledBackup() 方法已废弃，备份任务现在通过 SyncDbTask 统一管理");
        log.info("如果需要执行备份任务，请确保在 sync_db_sync 表中创建了对应的备份同步任务");
    }

    /**
     * 创建SQL备份进度回调
     */
    private SqlBackupGenerator.ProgressCallback createProgressCallback() {
        return new SqlBackupGenerator.ProgressCallback() {
            @Override
            public void onProgress(String currentTable, int tableIndex, int totalTables, long processedRows, long totalRows) {
                if (processedRows % 1000 == 0 || processedRows == totalRows) {
                    log.info("备份进度: 表 [{}/{}] {} - 已处理 {}/{} 行",
                            tableIndex + 1, totalTables, currentTable, processedRows, totalRows);
                }
            }

            @Override
            public void onTableStart(String tableName, long totalRows) {
                log.info("开始备份表: {} (共 {} 行)", tableName, totalRows);
            }

            @Override
            public void onTableComplete(String tableName, long processedRows) {
                log.info("完成备份表: {} (共 {} 行)", tableName, processedRows);
            }

            @Override
            public void onError(String message, Exception e) {
                log.error("备份过程中出现错误: {}", message, e);
            }
        };
    }
}